<?php
/**
 * Created by PhpStorm.
 * task: zhaoxiangyang
 * Date: 2019/11/29
 * Time: 下午5:18
 */

namespace app\store\model;

use think\Model;

class TradeModel extends Model
{
    protected $connection = 'db_platform_accounts';

    protected $table = 'tradelog';

    const PAY_UNPAID = 0;//未支付
    const PAY_PAID = 1;  //已支付

    /**通过日期和渠道获取充值数据
     * @param null $date
     * @param null $channel
     * @return mixed
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public static function getRechargeNumByDateAndChannel($date = null,$channel = null){
        // 结果是累计的 开始时间都是一致的
        if(empty($date)){
            $date = date('Y-m-d 00:00:00');
        }

        $date_end = date('Y-m-d 23:59:59',strtotime($date));

        // 次日
        $endtime_2 = date('Y-m-d H:i:s',strtotime($date)+2*24*3600-1);
        // 3日
        $endtime_3 = date('Y-m-d H:i:s',strtotime($date)+3*24*3600-1);
        // 7日
        $endtime_7 = date('Y-m-d H:i:s',strtotime($date)+7*24*3600-1);
        // 30日
        $endtime_30 = date('Y-m-d H:i:s',strtotime($date)+30*24*3600-1);
        // 每次的循环的次数不同 不能按照天数固定 应取当前时间与每天记录的日期相差天数
        $differ_date=ceil((time()-strtotime($date))/86400);
        $sql = "SELECT ";
        if($differ_date > 1){
            $sql.="count(distinct(case when
                createdat between '{$date}' and '{$endtime_2}'
                then userid
            end)) as day2";
        }

        if($differ_date > 2){
            $sql.=",count(distinct(case when
                createdat between '{$date}' and '{$endtime_3}'
                then userid
            end)) as day3";
        }

        if($differ_date > 6){
            $sql.=",count(distinct(case when
                createdat between '{$date}' and '{$endtime_7}'
                then userid
            end)) as day7";
        }

        if($differ_date > 29){
            $sql.=",count(distinct(case when
                createdat between '{$date}' and '{$endtime_30}'
                then userid
            end)) as day30";
        }

        $sql.= "
        FROM
            tradelog
        where (registerat between '{$date}' and '{$date_end}') and status = 1";
        if(!empty($channel)){
            $sql.=" and channel_id = {$channel}";
        }
        $res = self::query($sql);
        return $res[0];
    }

    
    /**获取指定时间的充值成功的用户
     * @param $where_time
     * @return array
     */
    public static function getUserIds($where_time){
        $data = self::where('paydate', 'between',$where_time)
            ->where('status', self::PAY_PAID)
            ->column('distinct(userid) as user_id');

        return $data;
    }

    /**获取指定时间的充值成功的用户的子查询
     * @param $where_time
     * @return string
     * @throws \think\exception\DbException
     */
    public static function getUserIdsSubSql($where_time){
        $sql = self::table('qpaccountsdb.tradelog')->where('paydate', 'between', $where_time)
            ->where('status', self::PAY_PAID)
            ->fieldRaw('distinct(userid)')
            ->buildSql();

        return $sql;
    }
}